﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Xml.XPath;
using System.Xml;
using System.Data.Common;
using System.Web; 

using umbraco;
using umbraco.presentation.umbracobase;

using UCommerce.Runtime;
using UCommerce.EntitiesV2;
using UCommerce.Xslt.Rendering;

using SubSonic.Schema;

using uCommerceContrib.Database;

namespace uCommerceContrib
{
    [XsltExtension]
    public class ProductHelper
    {
        public static int GetCategoryID(string sku)
        {
            var query = from relation in CategoryProductRelation.All()
                        join product in Product.All() on relation.ProductId equals product.ProductId
                        where product.Sku == sku && product.VariantSku == null
                        select relation;
            try
            {

                int catID = query.SingleOrDefault().Category.CategoryId;
                if (catID != 0)
                {
                    return (catID);
                }
                else
                {
                    return (-1);
                }
            }
            catch (Exception ex)
            {
                return (-1);
            }
        }

        public static string GetCategoryName(string sku)
        {
            var query = from relation in CategoryProductRelation.All()
                        join product in Product.All() on relation.ProductId equals product.ProductId
                        where product.Sku == sku && product.VariantSku == null
                        select relation;
            try
            {
                return (query.SingleOrDefault().Category.Name);
            }
            catch (Exception ex)
            {
                return ("");
            }            
        }

        public static int GetParentCategoryID(string sku)
        {
            var query = from relation in CategoryProductRelation.All()
                        join product in Product.All() on relation.ProductId equals product.ProductId
                        where product.Sku == sku
                        select relation;
            try
            {
                int parentCatID = query.FirstOrDefault().Category.ParentCategoryId.Value;
                if (parentCatID != 0)
                {
                    return (parentCatID);
                }
                else
                    return (-1);
                }
            catch (Exception ex)
            {
                return (-1);
            }
        }

        public static string GetParentCategoryName(string sku)
        {
            var query = from relation in CategoryProductRelation.All()
                        join product in Product.All() on relation.ProductId equals product.ProductId
                        where product.Sku == sku
                        select relation;
            try
            {
                int parentCatID = query.SingleOrDefault().Category.ParentCategoryId.Value;
                if (parentCatID != 0)
                {
                    var query2 = from category in Category.All()
                                 where category.CategoryId == parentCatID
                                 select category;
                    return (query2.Single().Name);
                }
                else
                    return ("");
            }
            catch (Exception ex)
            {
                return ("");
            }
        }

        public static string GetCatalogName(string sku)
        {
            var query = from relation in CategoryProductRelation.All()
                        join product in Product.All() on relation.ProductId equals product.ProductId
                        where product.Sku == sku
                        select relation;

            try
            {
                return (query.SingleOrDefault().Category.ProductCatalog.Name);
            }
            catch (Exception ex)
            {
                return ("");
            }
            
        }

        public static XPathNodeIterator GetTopSellers(int count)
        {
            try
            {
                List<Product> products = new List<Product>();
                StoredProcedure procedure = UCommerce.Entities.SPs.GetProductTop10();
                procedure.Command.Parameters.Add("ProductCatalogGroupId", null);
                DbDataReader dataReader = procedure.ExecuteReader();

                string sku = string.Empty;
                string variantSku = string.Empty;

                bool IsVariantSKU = DatabaseExtensions.HasColumn(dataReader, "VariantSKU");
                bool IsSKU = DatabaseExtensions.HasColumn(dataReader, "SKU");

                foreach (var data in dataReader)
                {
                    count--;
                    if (count < 0)
                        break;

                    if (IsVariantSKU)
                    {
                        variantSku = dataReader["VariantSKU"].ToString();
                        sku = dataReader["SKU"].ToString();

                        var product = Product.SingleOrDefault(p => p.Sku == sku && p.VariantSku == variantSku);
                        if (!product.Equals(null))
                        {
                            products.Add(product);
                        }
                    }
                    else
                    {
                        if (IsSKU)
                        {
                            sku = dataReader["SKU"].ToString();
                            var product = Product.SingleOrDefault(p => p.Sku == sku && p.VariantSku == null);
                            if (!product.Equals(null))
                            {
                                products.Add(product);
                            }
                        }
                    }
                }

                ProductRenderer pr = new ProductRenderer();
                if (products.Count() > 0)
                    return pr.RenderDocument(products, "products").CreateNavigator().Select(".");

                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.LoadXml("<products />");
                return xmlDoc.CreateNavigator().Select(".");


            }
            catch (Exception ex)
            {
                XmlDocument xmlDoc = new XmlDocument();
                xmlDoc.LoadXml("<error><message>" + ex.Message + "</message><stack>" + ex.StackTrace + "</stack></error>");
                return xmlDoc.CreateNavigator().Select(".");
            }
        }        
    }
}